/**
 * Copyright(c)2013 Beijing PeaceMap Co.,Ltd.
 * All right reserved. 
 */
package com.pmc.dwa.common.dao;

import java.lang.reflect.Field;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.math.NumberUtils;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.AbstractLobCreatingPreparedStatementCallback;
import org.springframework.jdbc.support.lob.LobCreator;

import com.pmc.dwa.common.annotation.PmcColumn;
import com.pmc.dwa.common.dialect.Dialect;
import com.pmc.dwa.common.dialect.DialectUtil;
import com.pmc.dwa.common.exception.DaoRuntimeException;
import com.pmc.dwa.common.exception.PmcRuntimeException;
import com.pmc.dwa.common.utils.BeanUtils;
import com.pmc.dwa.common.utils.PaginationSupport;
import com.pmc.dwa.common.utils.Util;

/**
 * @description 增强版数据库操作类[添加对clob和blob的部分操作]
 * @author aokunsang
 * @date 2013-7-4
 */
public class StGenericDao extends GenericDao {
	
	/**
	 * 查询单条记录
	 * @param <T>
	 * @param sql
	 * @param clazz
	 * @param args
	 * @return
	 */
	public <T> T _find(String sql,final Class<T> clazz,Object... args){
		printSqlInfo(sql,args!=null ? Arrays.toString(args) : "");
		final Field[] _fields = Util.spSelectSql(sql, clazz);
		try {
			return jdbcTemplate.queryForObject(sql,new RowMapper<T>(){
				@Override
				public T mapRow(ResultSet rs, int rownum) throws SQLException {
				    T bean = null;
					try {
						bean = clazz.newInstance();
						Field[] fields = _fields!=null ? _fields : clazz.getDeclaredFields();   //获取所有属性
						for(Field field : fields){
							if(field.getAnnotation(PmcColumn.class)==null) continue;
							switch(BeanUtils.getPmcColumnType(field)) {
								case Types.CLOB:{
									BeanUtils.setter(bean, field.getName(), lobHandler.getClobAsString(rs, BeanUtils.getPmcColumnName(field)), field.getType());
									break;
								}
								case Types.BLOB:{
									BeanUtils.setter(bean, field.getName(), lobHandler.getBlobAsBytes(rs, BeanUtils.getPmcColumnName(field)), field.getType());
									break;
								}
								case Types.TIMESTAMP:{
									BeanUtils.setter(bean, field.getName(),rs.getTimestamp(BeanUtils.getPmcColumnName(field)), field.getType());
									break;
								}
								case Types.DATE:{
									BeanUtils.setter(bean, field.getName(),rs.getDate(BeanUtils.getPmcColumnName(field)), field.getType());
									break;
								}
								case Types.INTEGER:{
									BeanUtils.setter(bean, field.getName(),rs.getInt(BeanUtils.getPmcColumnName(field)), field.getType());
									break;
								}
								case Types.VARCHAR:{
									BeanUtils.setter(bean, field.getName(),rs.getString(BeanUtils.getPmcColumnName(field)), field.getType());
									break;
								}
								case Types.BIGINT:{
							 		BeanUtils.setter(bean, field.getName(),rs.getLong(BeanUtils.getPmcColumnName(field)), field.getType());
									break;
								}
								case Types.FLOAT:{
									BeanUtils.setter(bean, field.getName(),rs.getFloat(BeanUtils.getPmcColumnName(field)), field.getType());
									break;
								}
								case Types.DOUBLE:{
									BeanUtils.setter(bean, field.getName(),rs.getDouble(BeanUtils.getPmcColumnName(field)), field.getType());
									break;
								}
							}
						}
					} catch (Exception e) {
						e.printStackTrace();
					}
					return bean;
				}
			},args);
		} catch (EmptyResultDataAccessException e) {
			return null;
		} catch (DataAccessException e) {
			throw new DaoRuntimeException("----------数据库错误saveOrUpdate()------", e);
		}
	}
	
	/**
	 * 分页查询记录列表
	 * @param <T>
	 * @param sql
	 * @param clazz
	 * @param pageNumber
	 * @param pageSize
	 * @param args
	 * @return
	 */
	public <T> PaginationSupport<T> _queryListByRows(String sql,final Class<T> clazz,int pageNumber,
			int pageSize,Object...args){
		String countSql = DialectUtil.getDialectInstance().getCountString(sql);   //总数sql语句
		Map<String,Object> countMap = findMap(countSql,args);
		long totalCount = countMap!=null && !countMap.isEmpty() ? NumberUtils.toLong(countMap.get(Dialect.COUNT_FIELD).toString(),0) : 0;
		//int pageCount = (int)(totalCount + pageSize - 1) / pageSize;
		//pageNumber = pageCount < pageNumber ? pageCount : (pageNumber <= 0 ? 1 : pageNumber);   //当前页码大于总页数，则查询最后一页
		int offset = (pageNumber - 1) * pageSize;                      //计算偏移量
		String fenyeSql = DialectUtil.getDialectInstance().getLimitString(sql, offset, pageSize); //分页sql语句
		List<T> list = _query(fenyeSql,clazz,args);
		return new PaginationSupport<T>(list, totalCount, pageNumber,pageSize);
	}
	/**
	 * 查询列表
	 * @param <T>
	 * @param sql
	 * @param clazz
	 * @param args
	 * @return
	 */
	public <T> List<T> _query(String sql,final Class<T> clazz,Object...args){
		printSqlInfo(sql,args!=null ? Arrays.toString(args) : "");  //打印分页语句
		final Field[] _fields = Util.spSelectSql(sql, clazz);
		return jdbcTemplate.query(sql, args, new RowMapper<T>(){
			@Override
			public T mapRow(ResultSet rs, int rownum) throws SQLException {
				T bean = null;
				try {
					bean = clazz.newInstance();
					Field[] fields = _fields!=null ? _fields : clazz.getDeclaredFields();   //获取所有属性
					for(Field field : fields){
						if(field.getAnnotation(PmcColumn.class)==null) continue;
						if(BeanUtils.getPmcColumnIgnore(field)) continue;   //是否忽视
						switch(BeanUtils.getPmcColumnType(field)) {
							case Types.CLOB:{
								BeanUtils.setter(bean, field.getName(), lobHandler.getClobAsString(rs, BeanUtils.getPmcColumnName(field)), field.getType());
								break;
							}
							case Types.BLOB:{
								BeanUtils.setter(bean, field.getName(), lobHandler.getBlobAsBytes(rs, BeanUtils.getPmcColumnName(field)), field.getType());
								break;
							}
							case Types.TIMESTAMP:{
								BeanUtils.setter(bean, field.getName(),rs.getTimestamp(BeanUtils.getPmcColumnName(field)), field.getType());
								break;
							}
							case Types.DATE:{
								BeanUtils.setter(bean, field.getName(),rs.getDate(BeanUtils.getPmcColumnName(field)), field.getType());
								break;
							}
							case Types.INTEGER:{
								BeanUtils.setter(bean, field.getName(),rs.getInt(BeanUtils.getPmcColumnName(field)), field.getType());
								break;
							}
							case Types.VARCHAR:{
								BeanUtils.setter(bean, field.getName(),rs.getString(BeanUtils.getPmcColumnName(field)), field.getType());
								break;
							}
							case Types.BIGINT:{
						 		BeanUtils.setter(bean, field.getName(),rs.getLong(BeanUtils.getPmcColumnName(field)), field.getType());
								break;
							}
							case Types.FLOAT:{
								BeanUtils.setter(bean, field.getName(),rs.getFloat(BeanUtils.getPmcColumnName(field)), field.getType());
								break;
							}
							case Types.DOUBLE:{
								BeanUtils.setter(bean, field.getName(),rs.getDouble(BeanUtils.getPmcColumnName(field)), field.getType());
								break;
							}
						}
					}
				} catch (Exception e) {
					e.printStackTrace();
				}
				return bean;
			}
		});
	}
	
	/**
	 * 添加或修改某条记录
	 * @param <T>
	 * @param sql
	 * @param bean
	 */
	public <T> void _saveOrUpdate(String sql,final T bean){
		final Map<String,Integer> columnTypes = new HashMap<String,Integer>();
		Map<String,String> columnMaps = new HashMap<String,String>();
		Field[] fields = bean.getClass().getDeclaredFields();   //获取所有属性
		for(Field field : fields){
			columnTypes.put(field.getName(), BeanUtils.getPmcColumnType(field));
			columnMaps.put(field.getName(), BeanUtils.getPmcColumnName(field));
		}
		final Map<String,List<String>> resultMap = Util.spQuerysql(sql,columnMaps);
		if(resultMap==null) throw new PmcRuntimeException(String.format("你提供的SQL语句有问题，请详细检查再次尝试运行。SQL：[s%]",sql));
		Object[] validSql = resultMap.keySet().toArray();   //改变后的Sql语句
		printSqlInfo(sql+"<<----->>"+validSql[0].toString(),"");   //打印改变前后的sql语句
		final List<String> fieldList = resultMap.get(validSql[0]);   //对应插入的bean字段
		jdbcTemplate.execute(validSql[0].toString(),new AbstractLobCreatingPreparedStatementCallback(this.lobHandler) {
			@Override
			protected void setValues(PreparedStatement ps, LobCreator lobCreater)
					throws SQLException, DataAccessException {
				for(int i=0,length=fieldList.size();i<length;i++){
					String filedValue = fieldList.get(i);
					Integer columnType = columnTypes.get(filedValue);
					if(columnType==null) throw new PmcRuntimeException(String.format("你提供的SQL语句有中存在不能识别的Bean属性字段，请详细检查再次尝试运行"));
					switch (columnType) {
						case Types.CLOB:{
							lobCreater.setClobAsString(ps, i+1, BeanUtils.getter(bean, filedValue)==null ? null : BeanUtils.getter(bean, filedValue).toString());
							break;
						}
						case Types.BLOB:{
							lobCreater.setBlobAsBytes(ps, i+1, BeanUtils.getter(bean, filedValue)==null ? null : (byte[])BeanUtils.getter(bean, filedValue));
							break;
						}
						case Types.TIMESTAMP:{
							ps.setTimestamp(i+1, BeanUtils.getter(bean, filedValue)==null ? null : (Timestamp)BeanUtils.getter(bean, filedValue));
							break;
						}
						case Types.DATE:{
							ps.setDate(i+1, BeanUtils.getter(bean, filedValue)==null ? null : (Date)BeanUtils.getter(bean, filedValue));
							break;
						}
						case Types.INTEGER:{
							ps.setObject(i+1, BeanUtils.getter(bean, filedValue)==null ? null : (Integer)BeanUtils.getter(bean, filedValue), Types.INTEGER);
							break;
						}
						case Types.VARCHAR:{
							ps.setString(i+1, BeanUtils.getter(bean, filedValue)==null ? null : BeanUtils.getter(bean, filedValue).toString());
							break;
						}
						case Types.BIGINT:{
							ps.setObject(i+1, BeanUtils.getter(bean, filedValue)==null ? null : (Long)BeanUtils.getter(bean, filedValue), Types.BIGINT);
							break;
						}
						case Types.FLOAT:{
							ps.setObject(i+1, BeanUtils.getter(bean, filedValue)==null ? null : (Float)BeanUtils.getter(bean, filedValue), Types.FLOAT);
							break;
						}
						case Types.DOUBLE:{
							ps.setObject(i+1, BeanUtils.getter(bean, filedValue)==null ? null : (Double)BeanUtils.getter(bean, filedValue), Types.DOUBLE);
							break;
						}
					}
				}
			}
		});
	}
}
